<?php
/**
 * 住所検索用関数<br />
 *
 * @package apamanshop
 * @author　
 * @copyright　
 */
class Search_Model_Selsikucyoson extends Search_Model_Base {

	/**
	* 建物種別取得
	*/
	public function getTatemonoTypeCd($params)
	{
		$result = array();

		if(array_key_exists('TatemonoTypeCd',$params) && $params['TatemonoTypeCd'] != "")
		{
			switch ($params['TatemonoTypeCd']){
				case 1:	// 貸家アパート (アパート、ハイツ、コーポ)
					$result=	array(Apm_Config_Search::TATEMONO_APART
														,Apm_Config_Search::TATEMONO_HEIGHTS
														,Apm_Config_Search::TATEMONO_CORP
														,Apm_Config_Search::TATEMONO_GESYUKU);
					break;
				case 2:	// 貸家マンション (マンション)
					$result =	array(Apm_Config_Search::TATEMONO_MANSION);
					break;
				case 3:	// 貸家一戸建て・借家 (テラスハウス、タウンハウス、一戸建て、貸家、長屋)
					$result =	array(Apm_Config_Search::TATEMONO_TERRACE
														 ,Apm_Config_Search::TATEMONO_TOWN
														,Apm_Config_Search::TATEMONO_IKKODATE
														,Apm_Config_Search::TATEMONO_KASIYA
														,Apm_Config_Search::TATEMONO_NAGAYA);
					break;
				default:	// 全建物種別
					$result =	array(Apm_Config_Search::TATEMONO_APART
														,Apm_Config_Search::TATEMONO_HEIGHTS
														,Apm_Config_Search::TATEMONO_CORP
														,Apm_Config_Search::TATEMONO_GESYUKU
														,Apm_Config_Search::TATEMONO_MANSION
														,Apm_Config_Search::TATEMONO_TERRACE
														,Apm_Config_Search::TATEMONO_TOWN
														,Apm_Config_Search::TATEMONO_IKKODATE
														,Apm_Config_Search::TATEMONO_KASIYA
														,Apm_Config_Search::TATEMONO_NAGAYA
														,Apm_Config_Search::TATEMONO_TENPO
														,Apm_Config_Search::TATEMONO_JIMUSYO
														,Apm_Config_Search::TATEMONO_TENPO_JIMUSYO
														,Apm_Config_Search::TATEMONO_TENPO_JYUTAKU
														,Apm_Config_Search::TATEMONO_JIGYO_TATEMONO
														,Apm_Config_Search::TATEMONO_JIGYO_TOCHI
														,Apm_Config_Search::TATEMONO_SONOTA_TOCHI
														,Apm_Config_Search::TATEMONO_TYUSYAJYO);
			}
		}

		return $result;
	}

	/**
	* 市区町村一覧データ取得
	*/
	public function getShikucyosonData($params)
    {
        $db_params = array();
        // 建物種別指定判定
        if(array_key_exists('TatemonoTypeCd',$params) && $params['TatemonoTypeCd'] != ""){
        	$sql = $this->generateShikucyosonTatemonoSql($params, $db_params);
        }else{	
		
			$sum = 0;	//都道府県の物件数を格納
		
			$sql = $this->generateShikucyosonSql($params, $db_params);
			$var = $this->dbGetList($sql, $db_params);
				
			// 市区町村の物件数の合計を都道府県の物件数として計算
			// ただし、都道府県の合計を物件数を計算しない設定ではREC_CNTに-1
			// が入るため、PREF_HEYA_CNTに集計数ではなく-1をセットする
			foreach ($var as $value){
				$sum += intval($value['REC_CNT']);
			}
			if($sum < 0){
				$sum = -1;
			}
			foreach ($var as &$value){
				$value['PREF_HEYA_CNT'] = $sum; 
			}

			return $var;
		}
		return $this->dbGetList($sql, $db_params);
    }

	/**
	* 市区町村一覧データ取得（リクエスト用）
	*/
	public function getRequestShikucyosonData($params)
    {
        $db_params = array();
        $sql = $this->generateShikucyosonSql($params, $db_params, false);
        
        return $this->dbGetList($sql, $db_params);
    }

	/**
	* 市区町村一覧データ取得（こだわり検索）
	*/
	public function getKodawariShikucyosonData($params)
    {
        $db_params = array();
        $sql = $this->generateShikucyosonKodawariSql($params, $db_params, false);
        return $this->dbGetList($sql, $db_params);
    }

	/**
	* 市区町村一覧データ取得（初期費用検索）
	*/
	public function getSyokihiyoShikucyosonData($params)
    {
        $db_params = array();
        $sql = $this->generateShikucyosonSyokihiyoSql($params, $db_params, false);
        return $this->dbGetList($sql, $db_params);
    }

	/**
	* 市区町村一覧データ取得（お宝キャンペーン検索）
	*/
	public function getOtakaraShikucyosonData($params)
    {
        $db_params = array();
        $sql = $this->generateShikucyosonOtakaraSql($params, $db_params, false);
        return $this->dbGetList($sql, $db_params);
    }
	/**
	* 市区町村一覧データ取得（キャンペーン検索）
	*/
	public function getCampaignShikucyosonData($params)
    {
        $db_params = array();
        $sql = $this->generateShikucyosonCampaignSql($params, $db_params, false);
        return $this->dbGetList($sql, $db_params);
    }

	/**
	* 政令指定都市コード取得
	*/
	public function getSiteiShikuCd($params)
    {
        $db_params = array();
        $sql = $this->generateSiteiShikuCdSql($params, $db_params);
        return $this->dbGetData($sql, $db_params);
    }

	/**
	* 市区町村Cdから政令指定都市データ取得
	*/
	public function getSiteiFromCityCd($params)
    {
        $db_params = array();
        $sql = $this->generateSiteiFromCityCdSql($params, $db_params);
        return $this->dbGetData($sql, $db_params);
    }

	/**
	* 政令指定都市コードから政令指定都市名取得
	*/
	public function getSiteiShikuNameBySiteiShikuCd($params)
    {
        $db_params = array();
        $sql = $this->generateSiteiShikuNameBySiteiShikuCdSql($params, $db_params);
        return $this->dbGetList($sql, $db_params);
    }

   /**
	* 市区町村一覧データを表示用に変換
	*
	* @access	public
	* @param	array ※参照渡し
	* @return	void
	*/
	public function convertShikucyosonData(&$data)
	{
			$data['Mapping'] = array();

		$beforeShikuchosonCd = null;

		foreach ($data['selcity'] as $cityData)
		{
			$todoufukenCd = $cityData['TODOUFUKEN_CD'];

			// 都道府県が異なる場合
			if (array_key_exists($todoufukenCd, $data['Mapping']) == false)
			{
				$data['Mapping']["$todoufukenCd"]['TODOUFUKEN_NAME']   = $cityData['TODOUFUKEN'];
				$data['Mapping']["$todoufukenCd"]['PREF_HEYA_CNT']     = $cityData['PREF_HEYA_CNT'];
				$data['Mapping']["$todoufukenCd"]['SITEITOSI_DATA']    = array();
				$data['Mapping']["$todoufukenCd"]['SHIKUCHOUSON_DATA'] = array();

			}

			// 指定都市CDが存在する場合
			if (!empty($cityData['SITEITOSI_CD']))
			{
				$siteitosiCd = $cityData['SITEITOSI_CD'];
				// 指定都市が異なる場合
				if (array_key_exists($todoufukenCd, $data['Mapping']["$todoufukenCd"]['SITEITOSI_DATA']) == false)
				{
					$data['Mapping']["$todoufukenCd"]['SITEITOSI_DATA']["$siteitosiCd"]['SITEITOSI_NAME']  = $cityData['SITEITOSI'];
					$data['Mapping']["$todoufukenCd"]['SITEITOSI_DATA']["$siteitosiCd"]['SITEITOSI_ROMAN'] = $cityData['SITEITOSI_ROMAN'];
					$data['Mapping']["$todoufukenCd"]['SITEITOSI_DATA']["$siteitosiCd"][] = array();
				}
				// 市区データ追加
				$data['Mapping']["$todoufukenCd"]['SITEITOSI_DATA']["$siteitosiCd"]['SITEI_SIKU_DATA'][] = array(
					'SHIKUCHOUSON'			=> $cityData['SHIKUCHOUSON'],
					'SHIKUCHOUSON_CD'		=> $cityData['SHIKUCHOUSON_CD'],
					'REC_CNT'				=> $cityData['REC_CNT'],
					'SHIKUCHOUSON_KANA' 	=> $cityData['SHIKUCHOUSON_KANA']
				);
			}
			else
			{
				$data['Mapping']["$todoufukenCd"]['SHIKUCHOUSON_DATA'][] = array(
					'SHIKUCHOUSON'			=> $cityData['SHIKUCHOUSON'],
					'SHIKUCHOUSON_CD'		=> $cityData['SHIKUCHOUSON_CD'],
					'REC_CNT'				=> $cityData['REC_CNT'],
					'SHIKUCHOUSON_KANA' 	=> $cityData['SHIKUCHOUSON_KANA']
				);
			}
		}
	}

   /**
     * 市区町村データ検索用SQL文の取得
     *
     * 市区町村一覧データ検索用SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @param	string $bknSearchFlg 1:物件の存在する市区町村のみ検索
     * @return	string 市区町村データ検索用SQL
     */
    public function generateShikucyosonSql($params, &$db_params, $bknSearchFlg=true)
    {
		//バインド変数のセット	
		$whereSql = ""; 		//都道府県CDの条件を格納する
		$tmpSql = "";			//都道府県CDの条件を計算するために使用
		$index = 0;				 //都道府県CDの数
		// データ区分
		$db_params['dataKubun'];
		if(!empty($params['shikireizero']) && $params['shikireizero'] == "on"){
			$db_params['dataKubun'] = 1;
		}else if(!empty($params['shikikin_nashi']) && $params['shikikin_nashi'] == "on" &&
					(!empty($params['reikin_nashi']) && $params['reikin_nashi'] == "on")){
			$db_params['dataKubun'] = 3;
		}else if(!empty($params['shikikin_nashi']) && $params['shikikin_nashi'] == "on"){
			$db_params['dataKubun'] = 4;
		}else if(!empty($params['reikin_nashi']) && $params['reikin_nashi'] == "on"){
			$db_params['dataKubun'] = 5;
		}else if(!empty($params['SrhKbn']) && $params['SrhKbn'] == Apm_Config_Search::SRHKBN_SYATAKU){
			$db_params['dataKubun'] = 2;
		}else{
			$db_params['dataKubun'] = 0;
		}	
		
		$sql = "";
		$sql .= "SELECT  /*+ INDEX(T57 IDX_T57SEARCH_SHIKUCHOUSON_01) */";
		$sql .= "     T57_TODOUFUKEN_CD AS TODOUFUKEN_CD";
		$sql .= "   , T57_TODOUFUKEN AS TODOUFUKEN";
		$sql .= "   , TRIM(T57_TODOUFUKEN_CD || T57_SHIKUCHOUSON_CD) AS SHIKUCHOUSON_CD";
		$sql .= "   , to_single_byte(T57_SHIKUCHOUSON) AS SHIKUCHOUSON";
		$sql .= "   , T57_SHIKUCHOUSON_KANA AS SHIKUCHOUSON_KANA ";
		$sql .= "   , T57_SITEITOSI_CD AS SITEITOSI_CD ";
		$sql .= "   , to_single_byte(T57_SITEITOSI) AS SITEITOSI ";
		$sql .= "   , T57_SITEITOSI_ROMAN AS SITEITOSI_ROMAN ";
		if ($bknSearchFlg) {
			$sql .= " , T57_SHIKUCHOUSON_CNT AS REC_CNT ";
		} else {
			$sql .= " , -1 AS REC_CNT ";
		}
		$sql .= "FROM ";
		$sql .= "   T57SEARCH_SHIKUCHOUSON_SUM T57";

		$sql .= " WHERE ";
		$sql .= "	T57_TODOUFUKEN_CD IN ". Apm_SqlUtil::generateWhereIn("PrefCD",$params['PrefCd'],$db_params);		
		$sql .= "   AND T57_DATA_KUBUN = :dataKubun ";
    	if ($bknSearchFlg)
		{
			$sql .= "AND T57_SHIKUCHOUSON_CNT > 0 ";
		}
		$sql .= "ORDER BY ";
//		$sql .= "   M43_TODOUFUKEN_CD ASC, M01.SITEITOSI_KANA, M01.M01_SHIKUCHOUSON_KANA ASC";
		$sql .= "   T57_TODOUFUKEN_CD ASC, T57_SHIKUCHOUSON_CD ";
		
		return $sql;
    }

   /**
     * 市区町村データ検索用SQL文の取得(建物種別指定)
     *
     * 市区町村一覧データ検索用SQL文の取得を行う(建物種別指定)
     * @access	public
     * @param	array
     * @param	array
     * @param	string $bknSearchFlg 1:物件の存在する市区町村のみ検索
     * @return	string 市区町村データ検索用SQL
     */
    public function generateShikucyosonTatemonoSql($params, $db_params, $bknSearchFlg=true)
    {
    	// 建物種別変換
        $params['typeCd'] = $this->getTatemonoTypeCd($params);

		$sql = "";
    	$sql .= "SELECT";
		$sql .= "        M43.M43_TODOUFUKEN_CD AS TODOUFUKEN_CD";
		$sql .= "        ,M43.M43_TODOUFUKEN AS TODOUFUKEN";
		$sql .= "        ,TRIM(M43.M43_TODOUFUKEN_CD || M01.M01_SHIKUCHOUSON_CD) AS SHIKUCHOUSON_CD";
		$sql .= "        ,to_single_byte(M01.M01_SHIKUCHOUSON) AS SHIKUCHOUSON";
		$sql .= "        ,M01.M01_SHIKUCHOUSON_KANA AS SHIKUCHOUSON_KANA";
		$sql .= "        ,M01.SITEITOSI_CD AS SITEITOSI_CD";
		$sql .= "        ,to_single_byte(M01.SITEITOSI) AS SITEITOSI";
		$sql .= "        ,M01.SITEITOSI_ROMAN";
		$sql .= "        ,A08_BUKKEN_CNT AS REC_CNT";
		$sql .= "    FROM";
		$sql .= "        (";
		$sql .= "            SELECT";
		$sql .= "                    A08_TODOUFUKEN_CD";
		$sql .= "                    ,A08_SHIKUCHOUSON_CD";
		$sql .= "                    ,SUM(A08_BUKKEN_CNT) AS A08_BUKKEN_CNT";
		$sql .= "                FROM";
		$sql .= "                    A08TATEMONO_BUKKEN_COUNT";
		$sql .= "                WHERE";
		$sql .=                      $this->createWhere("A08_TODOUFUKEN_CD",$params['PrefCd']);
		$sql .= "                    AND ".$this->createWhere("A08_TATEMONO_TYPE_CD",$params['typeCd']);
		$sql .= "                GROUP BY";
		$sql .= "                    A08_TODOUFUKEN_CD";
		$sql .= "                    ,A08_SHIKUCHOUSON_CD";
		$sql .= "        ) A08";
		$sql .= "        ,(";
		$sql .= "            SELECT";
		$sql .= "                    M43_TODOUFUKEN_CD";
		$sql .= "                    ,M43_TODOUFUKEN";
		$sql .= "                FROM";
		$sql .= "                    M43TODOUFUKEN";
		$sql .= "                WHERE";
		$sql .=                      $this->createWhere("M43_TODOUFUKEN_CD",$params['PrefCd']);
		$sql .= "        ) M43";
		$sql .= "        ,(";
		$sql .= "            SELECT";
		$sql .= "                    M01.M01_SHIKUCHOUSON_CD";
		$sql .= "                    ,M01.M01_TODOUFUKEN_CD";
		$sql .= "                    ,M01.M01_SHIKUCHOUSON";
		$sql .= "                    ,M01.M01_SHIKUCHOUSON_KANA";
		$sql .= "                    ,TOSI.M44_SITEITOSI_CD AS SITEITOSI_CD";
		$sql .= "                    ,TOSI.M44_SITEITOSI AS SITEITOSI";
		$sql .= "                    ,TOSI.M44_SITEITOSI_KANA AS SITEITOSI_KANA";
		$sql .= "                    ,TOSI.M44_SITEITOSI_ROMAN AS SITEITOSI_ROMAN";
		$sql .= "                FROM";
		$sql .= "                    M01JYUUSHO M01";
		$sql .= "                    ,M44SITEITOSI_SIKU SIKU";
		$sql .= "                    ,M44SITEITOSI TOSI";
		$sql .= "                WHERE";
		$sql .=                      $this->createWhere("M01.M01_TODOUFUKEN_CD",$params['PrefCd']);
		$sql .= "                    AND TRIM(M01.M01_ABOLISH_YM) IS NULL";
		$sql .= "                    AND M01.M01_TODOUFUKEN_CD = SIKU.M44_TODOUFUKEN_CD(+)";
		$sql .= "                    AND M01.M01_SHIKUCHOUSON_CD = SIKU.M44_SHIKUCHOUSON_CD(+)";
		$sql .= "                    AND SIKU.M44_SITEITOSI_CD = TOSI.M44_SITEITOSI_CD(+)";
		if(array_key_exists('SiteitosiCd', $params) && $params['SiteitosiCd'] != ""){
			$sql .= "                AND ".$this->createWhere("TOSI.M44_SITEITOSI_CD",$params['SiteitosiCd']);
		}
		$sql .= "                GROUP BY";
		$sql .= "                    M01.M01_SHIKUCHOUSON_CD";
		$sql .= "                    ,M01.M01_TODOUFUKEN_CD";
		$sql .= "                    ,M01.M01_SHIKUCHOUSON";
		$sql .= "                    ,M01.M01_SHIKUCHOUSON_KANA";
		$sql .= "                    ,TOSI.M44_SITEITOSI_CD";
		$sql .= "                    ,TOSI.M44_SITEITOSI";
		$sql .= "                    ,TOSI.M44_SITEITOSI_KANA";
		$sql .= "                    ,TOSI.M44_SITEITOSI_ROMAN";
		$sql .= "        ) M01";
		$sql .= "    WHERE";
		$sql .= "        M43.M43_TODOUFUKEN_CD = A08.A08_TODOUFUKEN_CD";
		$sql .= "        AND M43.M43_TODOUFUKEN_CD = M01.M01_TODOUFUKEN_CD";
		$sql .= "        AND M01.M01_SHIKUCHOUSON_CD = SUBSTR(A08.A08_SHIKUCHOUSON_CD, 3, 3)";
		$sql .= "    ORDER BY";
		$sql .= "        M43_TODOUFUKEN_CD ASC";
		$sql .= "        ,M01.M01_SHIKUCHOUSON_CD";

        return $sql;
    }

    /**
	* 市区町村データ検索用SQL文の取得(OEM検索用)
	*/
    public function generateShikucyosonOemSql($params)
    {

        //ｸｴﾘ作成(指定法人のﾃﾞｰﾀを取得する)
    	$sql = "";
		$sql .= "SELECT ";
		$sql .= "C.T03_TODOUFUKEN_CD AS TODOUFUKEN_CD,";
		$sql .= "C.M01_TODOUFUKEN AS TODOUFUKEN,";
		$sql .= "TRIM(C.T03_TODOUFUKEN_CD || C.T03_SHIKUCHOUSON_CD) AS SHIKUCHOUSON_CD,";
		$sql .= "to_single_byte(C.M01_SHIKUCHOUSON) AS SHIKUCHOUSON,";
		$sql .= "  C.REC_CNT AS REC_CNT,";
		$sql .= "  D.REC_CNT AS PREF_HEYA_CNT";
		$sql .= "FROM ";

		//OEM検索ｷｰﾜｰﾄﾞが8桁であった場合(=店舗検索)
		if(strlen($params['OemCd'][0]) == 8){
			$sql .= "( SELECT ";
			$sql .= "T03_TODOUFUKEN_CD, T03_SHIKUCHOUSON_CD, M01_TODOUFUKEN, M01_SHIKUCHOUSON, COUNT(*) AS REC_CNT  ";
			$sql .= "FROM T03WEB_HEYA, M05TENPO_INFO, M01JYUUSHO  ";
			$sql .= " WHERE";
			$sql .= $this->createWhere("T03_TODOUFUKEN_CD",$params['PrefCd']);
			$sql .= "AND T03_TODOUFUKEN_CD = M01_TODOUFUKEN_CD  ";
			$sql .= "AND T03_SHIKUCHOUSON_CD = M01_SHIKUCHOUSON_CD  ";
			$sql .= "AND T03_CHOUMEI_CD = M01_CHOUMEI_CD  ";
			$sql .= "AND T03_CHOUMOKU_CD = M01_CHOUMOKU_CD  ";
			$sql .= "AND  ";
			$sql .= $this->createWhere("M43_TODOUFUKEN_CD",$params['arrOemCd']);
			$sql .= "AND M05_TENPO_CD = T03_TENPO_CD ";
			$sql .= "AND ( M05_OEM = 2 OR M05_OEM = 3 ) ";
			$sql .= "GROUP BY T03_TODOUFUKEN_CD, T03_SHIKUCHOUSON_CD, M01_TODOUFUKEN, M01_SHIKUCHOUSON ) C, ";
			$sql .= " ( SELECT ";
			$sql .= " T03_TODOUFUKEN_CD, COUNT(*) AS REC_CNT ";
			$sql .= " FROM T03WEB_HEYA, M05TENPO_INFO ";
			$sql .= " WHERE";
			$sql .= $this->createWhere("T03_TODOUFUKEN_CD",$params['PrefCd']);
			$sql .= "  AND  ";
			$sql .= $this->createWhere("M43_TODOUFUKEN_CD",$params['arrOemCd']);
			$sql .= "  AND M05_TENPO_CD = T03_TENPO_CD ";
			$sql .= " AND ( M05_OEM = 2 OR M05_OEM = 3 ) ";
			$sql .= " GROUP BY T03_TODOUFUKEN_CD ) D ";
		}else{

			$sql .= "( SELECT ";
			$sql .= "T03_TODOUFUKEN_CD, T03_SHIKUCHOUSON_CD, M01_TODOUFUKEN, M01_SHIKUCHOUSON, COUNT(*) AS REC_CNT ";
			$sql .= "FROM T03WEB_HEYA, M05TENPO_INFO, M01JYUUSHO ";
			$sql .= " WHERE";
	  		$sql .= $this->createWhere("T03_TODOUFUKEN_CD",$params['PrefCd']);
			$sql .= " AND T03_TODOUFUKEN_CD = M01_TODOUFUKEN_CD ";
			$sql .= "AND T03_SHIKUCHOUSON_CD = M01_SHIKUCHOUSON_CD ";
			$sql .= "AND T03_CHOUMEI_CD = M01_CHOUMEI_CD ";
			$sql .= "AND T03_CHOUMOKU_CD = M01_CHOUMOKU_CD ";
			$sql .= "AND M05_TENPO_CD = T03_TENPO_CD ";
			$sql .= "  AND  ";
			$sql .= $this->createWhere("M43_TODOUFUKEN_CD",$params['arrOemCd']);
			$sql .= "AND ( M05_OEM = 1 OR M05_OEM = 3 ) ";
			$sql .= "GROUP BY T03_TODOUFUKEN_CD, T03_SHIKUCHOUSON_CD, M01_TODOUFUKEN, M01_SHIKUCHOUSON ) C,";
			$sql .= " ( SELECT ";
			$sql .= "T03_TODOUFUKEN_CD, COUNT(*) AS REC_CNT ";
			$sql .= "FROM T03WEB_HEYA, M05TENPO_INFO ";
			$sql .= " WHERE";
	  		$sql .= $this->createWhere("T03_TODOUFUKEN_CD",$params['PrefCd']);
			$sql .= "AND M05_TENPO_CD = T03_TENPO_CD ";
			$sql .= "  AND  ";
			$sql .= $this->createWhere("M43_TODOUFUKEN_CD",$params['arrOemCd']);
			$sql .= "AND ( M05_OEM = 1 OR M05_OEM = 3 ) ";
			$sql .= "GROUP BY T03_TODOUFUKEN_CD ) D ";

		}
		$sql .= "  WHERE C.T03_TODOUFUKEN_CD = D.T03_TODOUFUKEN_CD ";
		$sql .= "  ORDER BY C.T03_TODOUFUKEN_CD ASC, TRIM(C.T03_TODOUFUKEN_CD || C.T03_SHIKUCHOUSON_CD) ASC ";

		return $sql;
	}

    /**
	* 市区町村データ検索用SQL文の取得(リクエスト用)
	*/
    public function generateShikucyosonRequestSql($params)
    {
 			$sql .=" SELECT";
			$sql .= " TODOUFUKEN_CD,";
			$sql .= " TODOUFUKEN,";
 			$sql .=" TRIM(SHIKUCHOUSON_CD) AS SHIKUCHOUSON_CD,";
			$sql .= " to_single_byte(SHIKUCHOUSON) AS SHIKUCHOUSON,";
			$sql .= " SHIKUCHOUSON_KANA,";
			$sql .= " 0 AS REC_CNT, ";
 			$sql .=" 0 AS PREF_HEYA_CNT ";
 			$sql .=" FROM (";

 			//カウンター
 			$count = 0;
			// 都道府県CDを指定
			foreach($params['PrefCd'] as $PrefCd){

				if($count >= 1){
					$sql .= " UNION ";
				}

	 			$sql .= " SELECT ";
				$sql .= " M01_TODOUFUKEN_CD AS TODOUFUKEN_CD, ";
	 			$sql .= " M01_TODOUFUKEN AS TODOUFUKEN, ";
	 			$sql .= " TRIM(M01_TODOUFUKEN_CD||M01_SHIKUCHOUSON_CD) AS SHIKUCHOUSON_CD, ";
	 			$sql .= " M01_SHIKUCHOUSON AS SHIKUCHOUSON, ";
				$sql .= " M01_SHIKUCHOUSON_KANA AS SHIKUCHOUSON_KANA ";
	 			$sql .= " FROM";

	 			$sql .= " M01JYUUSHO" .trim($PrefCd);
			    $sql .= " M01JYUUSHO ";

	 			$sql .= " WHERE ";
	 			$sql .= " M01_SHIKUCHOUSON IS NOT NULL ";
	 			$sql .= " AND M01_CHOUMEI IS NOT NULL ";

				$sql .= " AND M01_ABOLISH_YM IS NULL ";
				$sql .= " AND M01_TODOUFUKEN_CD = '".trim($PrefCd)."' ";

				$sql .= " GROUP BY ";
				$sql .= " M01_TODOUFUKEN_CD, ";
				$sql .= " M01_TODOUFUKEN, ";
				$sql .= " M01_SHIKUCHOUSON_CD, ";
		 		$sql .= " M01_SHIKUCHOUSON, ";
				$sql .= " M01_SHIKUCHOUSON_KANA ";
				$count++;
			}

  		$sql .=" ) ORDER BY ";
  		$sql .=" TODOUFUKEN_CD, ";
  		$sql .=" SHIKUCHOUSON_KANA ";

  		return $sql;
	}

	/**
	* 市区町村データ検索用SQL文の取得(こだわり検索用)
	*/
    public function generateShikucyosonKodawariSql($params,&$data)
    {
    	$data['KodawariId'] = $params['KodawariId'];

    	$sql .= "SELECT";
		$sql .= "        M43.M43_TODOUFUKEN_CD AS TODOUFUKEN_CD";
		$sql .= "        ,M43.M43_TODOUFUKEN AS TODOUFUKEN";
		$sql .= "        ,TRIM(M43.M43_TODOUFUKEN_CD || M01.M01_SHIKUCHOUSON_CD) AS SHIKUCHOUSON_CD";
		$sql .= "        ,to_single_byte(M01.M01_SHIKUCHOUSON) AS SHIKUCHOUSON";
		$sql .= "        ,M01.M01_SHIKUCHOUSON_KANA AS SHIKUCHOUSON_KANA";
		$sql .= "        ,M01.SITEITOSI_CD AS SITEITOSI_CD";
		$sql .= "        ,to_single_byte(M01.SITEITOSI) AS SITEITOSI";
		$sql .= "        ,M01.SITEITOSI_ROMAN";
		$sql .= "        ,A04_BUKKEN_CNT AS REC_CNT";
		$sql .= "    FROM";
		$sql .= "        (";
		$sql .= "            SELECT";
		$sql .= "                    A04_TODOUFUKEN_CD";
		$sql .= "                    ,A04_SHIKUCHOUSON_CD";
		$sql .= "                    ,SUM(A04_BUKKEN_CNT) AS A04_BUKKEN_CNT";
		$sql .= "                FROM";
		$sql .= "                    A04KODAWARI_BUKKEN_COUNT";
		$sql .= "                WHERE";
		$sql .=                      $this->createWhere("A04_TODOUFUKEN_CD",$params['PrefCd']);
		$sql .= "                    AND A04_KODAWARI_CD = : KodawariId ";
		$sql .= "                GROUP BY";
		$sql .= "                    A04_TODOUFUKEN_CD";
		$sql .= "                    ,A04_SHIKUCHOUSON_CD";
		$sql .= "        ) A04";
		$sql .= "        ,(";
		$sql .= "            SELECT";
		$sql .= "                    M43_TODOUFUKEN_CD";
		$sql .= "                    ,M43_TODOUFUKEN";
		$sql .= "                FROM";
		$sql .= "                    M43TODOUFUKEN";
		$sql .= "                WHERE";
		$sql .=                      $this->createWhere("M43_TODOUFUKEN_CD",$params['PrefCd']);
		$sql .= "        ) M43";
		$sql .= "        ,(";
		$sql .= "            SELECT";
		$sql .= "                    M01.M01_SHIKUCHOUSON_CD";
		$sql .= "                    ,M01.M01_TODOUFUKEN_CD";
		$sql .= "                    ,M01.M01_SHIKUCHOUSON";
		$sql .= "                    ,M01.M01_SHIKUCHOUSON_KANA";
		$sql .= "                    ,TOSI.M44_SITEITOSI_CD AS SITEITOSI_CD";
		$sql .= "                    ,TOSI.M44_SITEITOSI AS SITEITOSI";
		$sql .= "                    ,TOSI.M44_SITEITOSI_KANA AS SITEITOSI_KANA";
		$sql .= "                    ,TOSI.M44_SITEITOSI_ROMAN AS SITEITOSI_ROMAN";
		$sql .= "                FROM";
		$sql .= "                    M01JYUUSHO M01";
		$sql .= "                    ,M44SITEITOSI_SIKU SIKU";
		$sql .= "                    ,M44SITEITOSI TOSI";
		$sql .= "                WHERE";
		$sql .=                      $this->createWhere("M01.M01_TODOUFUKEN_CD",$params['PrefCd']);
		$sql .= "                    AND TRIM(M01.M01_ABOLISH_YM) IS NULL";
		$sql .= "                    AND M01.M01_TODOUFUKEN_CD = SIKU.M44_TODOUFUKEN_CD(+)";
		$sql .= "                    AND M01.M01_SHIKUCHOUSON_CD = SIKU.M44_SHIKUCHOUSON_CD(+)";
		$sql .= "                    AND SIKU.M44_SITEITOSI_CD = TOSI.M44_SITEITOSI_CD(+)";
		if(array_key_exists('SiteitosiCd', $params) && $params['SiteitosiCd'] != ""){
			$sql .= "                AND ".$this->createWhere("TOSI.M44_SITEITOSI_CD",$params['SiteitosiCd']);
		}
		$sql .= "                GROUP BY";
		$sql .= "                    M01.M01_SHIKUCHOUSON_CD";
		$sql .= "                    ,M01.M01_TODOUFUKEN_CD";
		$sql .= "                    ,M01.M01_SHIKUCHOUSON";
		$sql .= "                    ,M01.M01_SHIKUCHOUSON_KANA";
		$sql .= "                    ,TOSI.M44_SITEITOSI_CD";
		$sql .= "                    ,TOSI.M44_SITEITOSI";
		$sql .= "                    ,TOSI.M44_SITEITOSI_KANA";
		$sql .= "                    ,TOSI.M44_SITEITOSI_ROMAN";
		$sql .= "        ) M01";
		$sql .= "    WHERE";
		$sql .= "        M43.M43_TODOUFUKEN_CD = A04.A04_TODOUFUKEN_CD";
		$sql .= "        AND M43.M43_TODOUFUKEN_CD = M01.M01_TODOUFUKEN_CD";
		$sql .= "        AND M01.M01_SHIKUCHOUSON_CD = SUBSTR(A04.A04_SHIKUCHOUSON_CD, 3, 3)";
		$sql .= "    ORDER BY";
		$sql .= "        M43_TODOUFUKEN_CD ASC";
		$sql .= "        ,M01.M01_SHIKUCHOUSON_CD";

		return $sql;
    }


	/**
	* 市区町村データ検索用SQL文の取得(初期費用検索用)
	*/
    public function generateShikucyosonSyokihiyoSql($params,&$data)
    {
    	// syokihiyo：99が0円のため、読み替える
    	if($params['syokihiyo'] == '99')
    	{
    		$data['syokihiyo'] = 0;
    	}
    	else
    	{
    		$data['syokihiyo'] = $params['syokihiyo']*10000;
		}

		$ar_sql = '';
		foreach ($params['PrefCd'] as $key => $val)
		{
$ar_sql[] = <<< DOC_END
SELECT
    M43.M43_TODOUFUKEN_CD AS TODOUFUKEN_CD ,
    M43.M43_TODOUFUKEN AS TODOUFUKEN ,
    TRIM(M43.M43_TODOUFUKEN_CD || M01.M01_SHIKUCHOUSON_CD) AS SHIKUCHOUSON_CD ,
    to_single_byte(M01.M01_SHIKUCHOUSON) AS SHIKUCHOUSON ,
    M01.M01_SHIKUCHOUSON_KANA AS SHIKUCHOUSON_KANA ,
    M01.SITEITOSI_CD AS SITEITOSI_CD ,
    to_single_byte(M01.SITEITOSI) AS SITEITOSI ,
    M01.SITEITOSI_ROMAN ,
    NVL(A07_BUKKEN_CNT, 0) AS REC_CNT
FROM
    A07SYOKIHIYOU_BUKKEN_COUNT A07,
    M43TODOUFUKEN M43,
	(SELECT
        M01.M01_TODOUFUKEN_CD||M01.M01_SHIKUCHOUSON_CD AS CD,
        M01.M01_SHIKUCHOUSON_CD ,
        M01.M01_TODOUFUKEN_CD ,
        M01.M01_SHIKUCHOUSON ,
        M01.M01_SHIKUCHOUSON_KANA ,
        TOSI.M44_SITEITOSI_CD AS SITEITOSI_CD ,
        TOSI.M44_SITEITOSI AS SITEITOSI ,
        TOSI.M44_SITEITOSI_KANA AS SITEITOSI_KANA ,
        TOSI.M44_SITEITOSI_ROMAN AS SITEITOSI_ROMAN
    FROM
        M01JYUUSHO M01 ,
        M44SITEITOSI_SIKU SIKU ,
        M44SITEITOSI TOSI
    WHERE
        M01_TODOUFUKEN_CD = $val AND
        TRIM(M01.M01_ABOLISH_YM) IS NULL AND
        M01.M01_TODOUFUKEN_CD = SIKU.M44_TODOUFUKEN_CD(+) AND
        M01.M01_SHIKUCHOUSON_CD = SIKU.M44_SHIKUCHOUSON_CD(+) AND
        SIKU.M44_SITEITOSI_CD = TOSI.M44_SITEITOSI_CD(+)
    GROUP BY
        M01.M01_SHIKUCHOUSON_CD ,
        M01.M01_TODOUFUKEN_CD ,
        M01.M01_SHIKUCHOUSON ,
        M01.M01_SHIKUCHOUSON_KANA ,
        TOSI.M44_SITEITOSI_CD ,
        TOSI.M44_SITEITOSI ,
        TOSI.M44_SITEITOSI_KANA ,
        TOSI.M44_SITEITOSI_ROMAN) M01
WHERE
	A07.A07_TODOUFUKEN_CD(+) = $val AND
	A07.A07_SYOKIHIYOU(+) = :Syokihiyo AND
	M43.M43_TODOUFUKEN_CD = $val AND
	M43.M43_TODOUFUKEN_CD = M01.M01_TODOUFUKEN_CD AND
    M01.M01_TODOUFUKEN_CD = A07.A07_TODOUFUKEN_CD(+) AND
	M01.CD = A07.A07_SHIKUCHOUSON_CD(+)
DOC_END;
	}

$sql = 'SELECT * FROM ( ';
$sql .= implode(' UNION ', $ar_sql);
$sql .= ' )  ';
$sql .= ' ORDER BY TODOUFUKEN_CD ASC ,SHIKUCHOUSON_CD ';
		return $sql;
    }

	/**
	* 市区町村データ検索用SQL文の取得(お宝キャンペーン検索用)
	*/
    public function generateShikucyosonOtakaraSql($params,&$data)
    {

		$ar_sql = '';
		$campaignCd = $params['campaign'][0];

		foreach ($params['PrefCd'] as $key => $val)
		{

$ar_sql[] = <<< DOC_END
SELECT
    M43.M43_TODOUFUKEN_CD AS TODOUFUKEN_CD ,
    M43.M43_TODOUFUKEN AS TODOUFUKEN ,
    TRIM(M43.M43_TODOUFUKEN_CD || M01.M01_SHIKUCHOUSON_CD) AS SHIKUCHOUSON_CD ,
    to_single_byte(M01.M01_SHIKUCHOUSON) AS SHIKUCHOUSON ,
    M01.M01_SHIKUCHOUSON_KANA AS SHIKUCHOUSON_KANA ,
    M01.SITEITOSI_CD AS SITEITOSI_CD ,
    to_single_byte(M01.SITEITOSI) AS SITEITOSI ,
    M01.SITEITOSI_ROMAN ,
    NVL(A09_BUKKEN_CNT, 0) AS REC_CNT
FROM
    A09OTAKARA_BUKKEN_COUNT A09,
    M43TODOUFUKEN M43,
	(SELECT
        M01.M01_TODOUFUKEN_CD||M01.M01_SHIKUCHOUSON_CD AS CD,
        M01.M01_SHIKUCHOUSON_CD ,
        M01.M01_TODOUFUKEN_CD ,
        M01.M01_SHIKUCHOUSON ,
        M01.M01_SHIKUCHOUSON_KANA ,
        TOSI.M44_SITEITOSI_CD AS SITEITOSI_CD ,
        TOSI.M44_SITEITOSI AS SITEITOSI ,
        TOSI.M44_SITEITOSI_KANA AS SITEITOSI_KANA ,
        TOSI.M44_SITEITOSI_ROMAN AS SITEITOSI_ROMAN
    FROM
        M01JYUUSHO M01 ,
        M44SITEITOSI_SIKU SIKU ,
        M44SITEITOSI TOSI
    WHERE
        M01_TODOUFUKEN_CD = $val AND
        TRIM(M01.M01_ABOLISH_YM) IS NULL AND
        M01.M01_TODOUFUKEN_CD = SIKU.M44_TODOUFUKEN_CD(+) AND
        M01.M01_SHIKUCHOUSON_CD = SIKU.M44_SHIKUCHOUSON_CD(+) AND
        SIKU.M44_SITEITOSI_CD = TOSI.M44_SITEITOSI_CD(+)
    GROUP BY
        M01.M01_SHIKUCHOUSON_CD ,
        M01.M01_TODOUFUKEN_CD ,
        M01.M01_SHIKUCHOUSON ,
        M01.M01_SHIKUCHOUSON_KANA ,
        TOSI.M44_SITEITOSI_CD ,
        TOSI.M44_SITEITOSI ,
        TOSI.M44_SITEITOSI_KANA ,
        TOSI.M44_SITEITOSI_ROMAN) M01
WHERE
	A09.A09_TODOUFUKEN_CD(+) = $val AND
	M43.M43_TODOUFUKEN_CD = $val AND
	M43.M43_TODOUFUKEN_CD = M01.M01_TODOUFUKEN_CD AND
    M01.M01_TODOUFUKEN_CD = A09.A09_TODOUFUKEN_CD(+) AND
	M01.CD = A09.A09_SHIKUCHOUSON_CD(+) AND
	A09.A09_CAMPAIGN_CD(+) = $campaignCd
DOC_END;

	}

	$sql = 'SELECT * FROM ( ';
	$sql .= implode(' UNION ', $ar_sql);
	$sql .= ' )  ';
	$sql .= ' ORDER BY TODOUFUKEN_CD ASC ,SHIKUCHOUSON_CD ';

	return $sql;
    }

	/**
	* 市区町村データ検索用SQL文の取得(キャンペーン検索用)
	*/
    public function generateShikucyosonCampaignSql($params,&$data)
    {

		$ar_sql = '';
		$campaignCd = $params['campaign'][0];

		foreach ($params['PrefCd'] as $key => $val)
		{

$ar_sql[] = <<< DOC_END
SELECT
    M43.M43_TODOUFUKEN_CD AS TODOUFUKEN_CD ,
    M43.M43_TODOUFUKEN AS TODOUFUKEN ,
    TRIM(M43.M43_TODOUFUKEN_CD || M01.M01_SHIKUCHOUSON_CD) AS SHIKUCHOUSON_CD ,
    to_single_byte(M01.M01_SHIKUCHOUSON) AS SHIKUCHOUSON ,
    M01.M01_SHIKUCHOUSON_KANA AS SHIKUCHOUSON_KANA ,
    M01.SITEITOSI_CD AS SITEITOSI_CD ,
    to_single_byte(M01.SITEITOSI) AS SITEITOSI ,
    M01.SITEITOSI_ROMAN ,
    NVL(A10_BUKKEN_CNT, 0) AS REC_CNT
FROM
    A10CAMPAIGN_BUKKEN_COUNT_N A10,
    M43TODOUFUKEN M43,
	(SELECT
        M01.M01_TODOUFUKEN_CD||M01.M01_SHIKUCHOUSON_CD AS CD,
        M01.M01_SHIKUCHOUSON_CD ,
        M01.M01_TODOUFUKEN_CD ,
        M01.M01_SHIKUCHOUSON ,
        M01.M01_SHIKUCHOUSON_KANA ,
        TOSI.M44_SITEITOSI_CD AS SITEITOSI_CD ,
        TOSI.M44_SITEITOSI AS SITEITOSI ,
        TOSI.M44_SITEITOSI_KANA AS SITEITOSI_KANA ,
        TOSI.M44_SITEITOSI_ROMAN AS SITEITOSI_ROMAN
    FROM
        M01JYUUSHO M01 ,
        M44SITEITOSI_SIKU SIKU ,
        M44SITEITOSI TOSI
    WHERE
        M01_TODOUFUKEN_CD = $val AND
        TRIM(M01.M01_ABOLISH_YM) IS NULL AND
        M01.M01_TODOUFUKEN_CD = SIKU.M44_TODOUFUKEN_CD(+) AND
        M01.M01_SHIKUCHOUSON_CD = SIKU.M44_SHIKUCHOUSON_CD(+) AND
        SIKU.M44_SITEITOSI_CD = TOSI.M44_SITEITOSI_CD(+)
    GROUP BY
        M01.M01_SHIKUCHOUSON_CD ,
        M01.M01_TODOUFUKEN_CD ,
        M01.M01_SHIKUCHOUSON ,
        M01.M01_SHIKUCHOUSON_KANA ,
        TOSI.M44_SITEITOSI_CD ,
        TOSI.M44_SITEITOSI ,
        TOSI.M44_SITEITOSI_KANA ,
        TOSI.M44_SITEITOSI_ROMAN) M01
WHERE
	A10.A10_TODOUFUKEN_CD(+) = $val AND
	M43.M43_TODOUFUKEN_CD = $val AND
	M43.M43_TODOUFUKEN_CD = M01.M01_TODOUFUKEN_CD AND
    M01.M01_TODOUFUKEN_CD = A10.A10_TODOUFUKEN_CD(+) AND
	M01.CD = A10.A10_SHIKUCHOUSON_CD(+) AND
	A10.A10_CAMPAIGN_CD(+) = $campaignCd
DOC_END;

		}

		$sql = 'SELECT * FROM ( ';
		$sql .= implode(' UNION ', $ar_sql);
		$sql .= ' )  ';
		$sql .= ' ORDER BY TODOUFUKEN_CD ASC ,SHIKUCHOUSON_CD ';

		return $sql;
    }

	/**
	* 政令指定都市・市区一覧データ取得
	*/
	public function getSiteiShikuData($params, $bknSearchFlg)
    {
        //変数の宣言
		$db_params = array();
		$sum = 0;		//都道府県の物件数を格納
        
		$sql = $this->generateSiteiShikuSql($params, $db_params, $bknSearchFlg);
		$var = $this->dbGetList($sql, $db_params);
		
		// 市区町村の物件数の合計を都道府県の物件数として計算する
		foreach ($var as $value){
			$sum += intval($value['REC_CNT']);
		}
		foreach ($var as &$value){
			$value['PREF_HEYA_CNT'] = $sum;
		}	
		
        return $var;
    }


    /**
     * 政令指定都市・市区データ検索用SQL文の取得
     *
     * 政令指定都市・市区一覧データ検索用SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @param	string $bknSearchFlg 1:物件の存在する市区町村のみ検索
     * @return	string 市区町村データ検索用SQL
     */
    public function generateSiteiShikuSql($params, &$db_params, $bknSearchFlg)
    {
		//バインド変数のセット	
		$wherePrefSql = ""; 			//都道府県CDの条件を格納する
		$whereSiteiShikufSql = ""; 		//指定都市CDの条件を格納する		
		$tmpSql = "";					//都道府県CDの条件を計算するために使用
		$index = 0;						 //都道府県CDもしくは政令指定都市CDの数
		//バインド変数のセット		
		// データ区分
		$db_params['dataKubun'] = 0;		
			
		$sql = "";
		$sql .= "SELECT /*+ INDEX(T57 IDX_T57SEARCH_SHIKUCHOUSON_02) */ ";
		$sql .= "     T57_TODOUFUKEN_CD AS TODOUFUKEN_CD ";
		$sql .= "    ,T57_TODOUFUKEN AS TODOUFUKEN ";
		$sql .= "    ,TRIM(T57_TODOUFUKEN_CD || T57_SHIKUCHOUSON_CD) AS SHIKUCHOUSON_CD ";
		$sql .= "    ,to_single_byte(T57_SHIKUCHOUSON) AS SHIKUCHOUSON ";
		$sql .= "    ,T57_SITEITOSI_CD AS SITEITOSI_CD ";
		$sql .= "    ,to_single_byte(T57_SITEITOSI) AS SITEITOSI ";
		$sql .= "    ,T57_SHIKUCHOUSON_CNT AS REC_CNT ";
		$sql .= " FROM ";
		$sql .= "    T57SEARCH_SHIKUCHOUSON_SUM T57 ";
		$sql .= " WHERE ";
		$sql .= "		T57_TODOUFUKEN_CD IN ". Apm_SqlUtil::generateWhereIn("PrefCD",$params['PrefCd'],$db_params);		
		$sql .= "   AND T57_SITEITOSI_CD IN " . Apm_SqlUtil::generateWhereIn("SiteiCD",$params['SiteitosiCd'],$db_params);	
		$sql .= "   AND T57_DATA_KUBUN = :dataKubun ";
		if ($bknSearchFlg == '1')
		{
			$sql .= "AND T57_SHIKUCHOUSON_CNT > 0 ";
		}
		$sql .= "ORDER BY ";
		$sql .= "     T57_TODOUFUKEN_CD ASC ";
		$sql .= "    ,T57_SITEITOSI_KANA ASC ";
		$sql .= "    ,T57_SHIKUCHOUSON_KANA ASC ";

        return $sql;
    }

    /**
     * 市区町村名を名前順にソートする
     *
     * １：読み仮名の前一字から、アワ行に振り分ける
     * ２：各行にカテゴリ化した名前を読み仮名でソートする
     *
     * @access  public
     * @param   array    $data    市区町村データ
     * @param   array    $prefcd  都道府県データ
     * @return  array    $return  ソート済み市区町村データ
     */
    public function sortShikucyosonData(&$data, $prefcd)
    {

    	$category_data = array();

    	// 都道府県
    	foreach($prefcd as $pref_val){

    		// 市区町村データループ開始
    		foreach($data[$pref_val]['SHIKUCHOUSON_DATA'] as $shikucyouson_val){

    			$word = substr($shikucyouson_val['SHIKUCHOUSON_KANA'], 0, 1);

    			foreach(Apm_Config_Search::$TITLE_CATEGORY as $category_key => $category_val){

					if(array_search($word, $category_val['data'])){

						$shikucyouson_val['CATEGORY_TITLE'] = $category_val['title'];

						$category_data[$pref_val]['SHIKUCHOUSON_DATA'][$category_key][] = $shikucyouson_val;
						break;

					}
    			}
    		}

			// 2次元キーを昇順にする
    		ksort($category_data[$pref_val]['SHIKUCHOUSON_DATA']);

    	}

    	// 市区町村カナで昇順にする
    	foreach($category_data as $key => $val){

			$SHIKUCHOUSON_KANA = array();
			foreach ($val as $v) $SHIKUCHOUSON_KANA[] = $v['SHIKUCHOUSON_KANA'];

			array_multisort($SHIKUCHOUSON_KANA, SORT_ASC, SORT_STRING, $val);
			$category_data[$key] = $val;
    	}

    	// テンプレート変数にデータを渡す
    	$data = $category_data;

    }

    /**
     * 政令指定都市・市区コード取得用SQL文の取得
     *
     * 政令指定都市・市区コード取得SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @return	string 政令指定都市コード取得SQL
     */
    public function generateSiteiShikuCdSql($params, &$db_params)
    {

    	$db_params['siteitosiName'] = $params['SiteitosiName'];

		$sql = "";
		$sql .= "SELECT ";
		$sql .= "    M44_SITEITOSI_CD ";
		$sql .= "FROM ";
		$sql .= "    M44SITEITOSI ";
		$sql .= "WHERE ";
		$sql .= "    M44_SITEITOSI_ROMAN = :siteitosiName ";

        return $sql;
    }

    /**
     * 政令指定都市・市区コード取得用SQL文の取得
     *
     * 政令指定都市・市区コード取得SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @return	string 政令指定都市名取得SQL
     */
    public function generateSiteiShikuNameBySiteiShikuCdSql($params, &$db_params)
    {

        foreach($params['SiteitosiCd'] as $key => $val){
        	$db_params['siteitosiCd'.$key] = $val;
        }

		$sql = "";
		$sql .= "SELECT ";
		$sql .= "    to_single_byte(M44_SITEITOSI) M44_SITEITOSI ";
		$sql .= "   ,M44_SITEITOSI_ALIAS ";
		$sql .= "FROM ";
		$sql .= "    M44SITEITOSI ";
		$sql .= "WHERE ";
		$sql .= "    M44_SITEITOSI_CD IN ( ";

        $sepa = "";
        foreach($params['SiteitosiCd'] as $key => $val){
        	$sql .= $sepa . " :siteitosiCd".$key;
        	$sepa = ",";
        }
        $sql .= " )";
        $sql .= "ORDER BY ";
        $sql .= "    M44_SITEITOSI_CD ";

        return $sql;
    }

    /**
     * 政令指定都市データ取得用SQL文の取得
     *
     * 政令指定都市データ取得SQL文の取得を行う
     * @access	public
     * @param	array
     * @param	array
     * @return	string 政令指定都市データ取得SQL
     */
	public function generateSiteiFromCityCdSql($params, &$db_params)
	{
		$db_params['cityCd'] = $params;

		$sql = "";
		$sql .= "SELECT ";
		$sql .= "      TOSI.M44_SITEITOSI_CD ";
		$sql .= "    , to_single_byte(TOSI.M44_SITEITOSI) M44_SITEITOSI ";
		$sql .= "    , TOSI.M44_SITEITOSI_ROMAN ";
		$sql .= "FROM ";
		$sql .= "      M44SITEITOSI_SIKU SIKU ";
		$sql .= "    , M44SITEITOSI TOSI ";
		$sql .= "WHERE ";
		$sql .= "      SIKU.M44_TODOUFUKEN_CD = substr(:cityCd, 0, 2) ";
		$sql .= "  AND SIKU.M44_SHIKUCHOUSON_CD = substr(:cityCd, 3, 3) ";
		$sql .= "  AND SIKU.M44_SITEITOSI_CD = TOSI.M44_SITEITOSI_CD ";

        return $sql;
	}
	
	/*
	 * 人気のエリアから探す
	 * X件
	 * 
	 */
	public function getNinkiShikucyosonData($params){
	    
	    $db_params = array();
	    
	    // データ区分
	    $db_params['dataKubun'];
	    if(!empty($params['shikireizero']) && $params['shikireizero'] == "on"){
	        $db_params['dataKubun'] = 1;
	    }else if(!empty($params['shikikin_nashi']) && $params['shikikin_nashi'] == "on" &&
	        (!empty($params['reikin_nashi']) && $params['reikin_nashi'] == "on")){
	        $db_params['dataKubun'] = 3;
	    }else if(!empty($params['shikikin_nashi']) && $params['shikikin_nashi'] == "on"){
	        $db_params['dataKubun'] = 4;
	    }else if(!empty($params['reikin_nashi']) && $params['reikin_nashi'] == "on"){
	        $db_params['dataKubun'] = 5;
	    }else if(!empty($params['SrhKbn']) && $params['SrhKbn'] == Apm_Config_Search::SRHKBN_SYATAKU){
	        $db_params['dataKubun'] = 2;
	    }else{
	        $db_params['dataKubun'] = 0;
	    }
	    
	    $sql = "";
		$sql .= "SELECT T57_TODOUFUKEN_CD AS TODOUFUKEN_CD ";
		$sql .= "    ,TRIM(T57_TODOUFUKEN_CD || T57_SHIKUCHOUSON_CD) AS SHIKUCHOUSON_CD ";
		$sql .= "    ,to_single_byte(T57_SHIKUCHOUSON) AS SHIKUCHOUSON ";
		$sql .= "    ,T57_SITEITOSI_CD AS SITEITOSI_CD ";
		$sql .= " FROM ";
		$sql .= "    T57SEARCH_SHIKUCHOUSON_SUM T57 ";
		$sql .= " WHERE ";
		$sql .= " T57_DATA_KUBUN = :dataKubun ";
		$sql .= " AND T57_SHIKUCHOUSON_CNT > 0 ";
		
		//都道府県ごとの人気エリア
		if(isset($params['PrefCd']) && $params['PrefCd'] != ''){
		    $sql .= " AND T57_TODOUFUKEN_CD = '".$params['PrefCd']."'";
		}
		$sql .= " ORDER BY DBMS_RANDOM.RANDOM ";

        $sql = " SELECT * FROM ( " . $sql . " ) WHERE ROWNUM <= ".$params['listCount']; //トップｎ件          
	    $var = $this->dbGetList($sql, $db_params);
	    
	    return $var;
	}
	
	// 20141220 S_Add
	/**
	 * エリア検索（左パネル）のキーワードから、市区町村を取得
	 * @param unknown_type $params
	 */
	public function getSearchShikucyoson($params)
	{
		$sql = "";
		$sql .= "SELECT";
		$sql .= "     T57_TODOUFUKEN_CD AS TODOUFUKEN_CD";
		$sql .= "   , T57_TODOUFUKEN AS TODOUFUKEN";
		$sql .= "   , TRIM(T57_TODOUFUKEN_CD || T57_SHIKUCHOUSON_CD) AS SHIKUCHOUSON_CD";
		$sql .= "   , to_single_byte(T57_SHIKUCHOUSON) AS SHIKUCHOUSON";
		$sql .= "   , T57_SHIKUCHOUSON_KANA AS SHIKUCHOUSON_KANA ";
		$sql .= "   , T57_SITEITOSI_CD AS SITEITOSI_CD ";
		$sql .= "   , to_single_byte(T57_SITEITOSI) AS SITEITOSI ";
		$sql .= "   , T57_SITEITOSI_ROMAN AS SITEITOSI_ROMAN ";
		$sql .= "FROM ";
		$sql .= "   T57SEARCH_SHIKUCHOUSON_SUM T57";
	
		$sql .= " WHERE ";
		// 		$sql .= "	T57_TODOUFUKEN_CD IN ". Apm_SqlUtil::generateWhereIn("PrefCD",$params['PrefCd'],$db_params);
		$sql .= "	T57_TODOUFUKEN_CD = ";
		$sql .= "'" . $params['prefcd'] . "' ";
		// 		$sql .= "   AND T57_DATA_KUBUN = :dataKubun ";
		$sql .= "   AND T57_DATA_KUBUN = 1 ";
		$sql .= "ORDER BY ";
		//		$sql .= "   M43_TODOUFUKEN_CD ASC, M01.SITEITOSI_KANA, M01.M01_SHIKUCHOUSON_KANA ASC";
		$sql .= "   T57_TODOUFUKEN_CD ASC, T57_SHIKUCHOUSON_CD ";
	
		return $this->dbGetList($sql, $db_params);
	}
	// 20141220 E_Add
}
?>